/*   ro-questions, a plugin for the Rolgps, part of the RolGPS Proyect
 *   Copyright (C) 2009 Pablo Alejandro Costesich <rhfixer@gmail.com>
 *
 *   This program is free software: you can redistribute it and/or modify
 *   it under the terms of the GNU General Public License as published by
 *   the Free Software Foundation, either version 3 of the License, or
 *   (at your option) any later version.
 *
 *   This program is distributed in the hope that it will be useful,
 *   but WITHOUT ANY WARRANTY; without even the implied warranty of
 *   MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
 *   GNU General Public License for more details.
 * 
 * You should have received a copy of the GNU General Public
 * License along with this library; if not, write to the Free Software
 * Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston, MA  02110-1301, USA
 */
package dataTypes;

import databaseHandler.Database;
import deployers.sqlite.Deployer;
import deployers.sqlite.Field;
import deployers.sqlite.Table;
import java.io.File;
import java.io.IOException;
import java.sql.SQLException;
import java.util.logging.Level;
import java.util.logging.Logger;

/**
 *
 * @author xeph
 */
public class SearchEngine extends Database {
    


    public SearchEngine(File databaseFile) {
        setDatabaseFile(databaseFile);
    }
    
    private Language getLanguageById(String id){
        Language lang = new Language();
        StringBuffer query = new StringBuffer();
        query.append("SELECT id, name FROM languages");
        if(!id.isEmpty()){
             query.append(" WHERE id="+id );
        }
        query.append(" LIMIT 1;");

        try{
            Object[][] tempResult = executeStatement(query.toString());
            if(tempResult.length>0){
                lang.setId(tempResult[0][0].toString());
                lang.setName(tempResult[0][1].toString());
            }
        }catch(SQLException e){
            System.err.println("INTERNAL ERROR in getLanguageById query.\n"+query.toString());
        }
        
        return lang;
    }
    
    public Language[] getAllLanguages(){
        Language[] lang = new Language[0];
        StringBuffer query = new StringBuffer();
        query.append("SELECT id, name FROM languages");

        try{
            Object[][] tempResult = executeStatement(query.toString());
            lang = new Language[tempResult.length+1];
            lang[0] = new Language();
            lang[0].setId("");
            lang[0].setName("Cualquiera");
            int iterator = 1;
            for(Object[] eachResult : tempResult){
                lang[iterator] = new Language();
                lang[iterator].setId(eachResult[0].toString());
                lang[iterator].setName(eachResult[1].toString());
                iterator++;
            }
        }catch(SQLException e){
            System.err.println("INTERNAL ERROR in getAllLanguages query.");
        }
        return lang;
    }

    
    public Article[] searchArticle(Article modelo){
        Article[] result = new Article[0];
        StringBuffer query = new StringBuffer();
        query.append("SELECT id, title, body, languageid FROM articles WHERE 1=1");
        if (!modelo.getId().isEmpty()) {
            query.append(" AND id=" + escapeSql(modelo.getId()));
        }
        if (!modelo.getTitle().isEmpty()) {
            query.append(" AND title like %\'" + escapeSql(modelo.getTitle() )+ "\'");
        }
        if (!modelo.getBody().isEmpty()) {
            query.append(" AND body like %\'" + escapeSql(modelo.getBody()) + "\'");
        }
        if (!modelo.getLanguage().getId().isEmpty()) {
            query.append(" AND languageid =" + escapeSql(modelo.getLanguage().getId()));
        }

        query.append(";");
        try {

            Object[][] tempResult = executeStatement(query.toString());
            result = new Article[tempResult.length];
            int iterator = 0;
            for(Object[] asd : tempResult){
                result[iterator]=new Article();
                result[iterator].setId(asd[0].toString());
                result[iterator].setTitle(asd[1].toString());
                result[iterator].setBody(asd[2].toString());
                Language temp = getLanguageById(asd[3].toString());
                result[iterator].setLanguage(temp);
                iterator++;
            }

        } catch (SQLException ex) {
            Logger.getLogger(SearchEngine.class.getName()).log(Level.SEVERE, null, ex);
            System.err.println("INTERNAL ERROR in searchArticle query.\n"+(query.toString()));
            
        }
        return result;
    }
    
    public Article[] searchArticleInTitleOrBody(String searchString, Language lang){

        Article[] result = new Article[0];
        StringBuffer query = new StringBuffer();
        query.append("SELECT id, title, body, languageid FROM articles WHERE " + 
                "(title like \'%" + escapeSql(searchString) + "%\' OR " + 
                "body like \'%" + escapeSql(searchString) + "%\')");

        if (!lang.getId().isEmpty()) {
            query.append(" AND languageid=" + lang.getId());
        }
        query.append(";");
        
        try {
            //String[] values = {"a", "a"};
            //System.out.println(escapeQuery("Select * from articles where title like '%?%' or body like '%?%';", values));
            Object[][] tempResult = executeStatement(query.toString());
            result = new Article[tempResult.length];
            int iterator = 0;
            for (Object[] asd : tempResult) {
                result[iterator] = new Article();
                result[iterator].setId(asd[0].toString());
                result[iterator].setTitle(asd[1].toString());
                result[iterator].setBody(asd[2].toString());
                Language temp = getLanguageById(asd[3].toString());
                result[iterator].setLanguage(temp);
                iterator++;
            }
        } catch (SQLException ex) {
            Logger.getLogger(SearchEngine.class.getName()).log(Level.SEVERE, null, ex);
            System.err.println("INTERNAL ERROR in searchArticleInTitleOrBody query.\n" + (query.toString()));
        }
        return result;

    }
    
    public void deploy() {
        try {
            Deployer deployer = new Deployer(getCurrentDatabaseFile());

            Field id = new Field("id");
            id.addType(Field.IDFIELD);

            Field title = new Field("title");
            title.addType(Field.STRING);

            Field body = new Field("body");
            body.addType(Field.STRING);

            Field languageid = new Field("languageid");
            languageid.addType(Field.INTEGER);

            Table articles = new Table("articles");
            articles.addField(id, title, body, languageid);

            Field name = new Field("name");
            name.addType(Field.STRING);

            Table languages = new Table("languages");
            languages.addField(id, name);

            Field thisarticle = new Field("articleid");
            thisarticle.addType(Field.INTEGER);

            Field otherarticle = new Field("relatedid");
            otherarticle.addType(Field.INTEGER);

            Table related = new Table("related_articles");
            related.addField(id, thisarticle, otherarticle);
            
            deployer.addTable(articles, languages, related);

            deployer.deploy();

        } catch (IOException ex) {
            Logger.getLogger(SearchEngine.class.getName()).log(Level.SEVERE, null, ex);
        }

    }
}
